package com.skyeye.mapper;

import com.skyeye.entity.User;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 用户数据访问层接口（注解方式）
 * 使用MyBatis注解直接在接口中编写SQL，无需XML映射文件
 */
@Mapper
public interface UserMapperAnnotation {
    
    /**
     * 插入用户
     * @param user 用户对象
     * @return 影响行数
     */
    @Insert("INSERT INTO users (username, email, age, phone, create_time, update_time) " +
            "VALUES (#{username}, #{email}, #{age}, #{phone}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    int insertUser(User user);
    
    /**
     * 根据ID删除用户
     * @param id 用户ID
     * @return 影响行数
     */
    @Delete("DELETE FROM users WHERE id = #{id}")
    int deleteUserById(@Param("id") Integer id);
    
    /**
     * 更新用户信息（动态SQL）
     * @param user 用户对象
     * @return 影响行数
     */
    @Update("<script>" +
            "UPDATE users " +
            "<set>" +
            "<if test='username != null and username != \"\"'>username = #{username},</if>" +
            "<if test='email != null and email != \"\"'>email = #{email},</if>" +
            "<if test='age != null'>age = #{age},</if>" +
            "<if test='phone != null and phone != \"\"'>phone = #{phone},</if>" +
            "<if test='updateTime != null'>update_time = #{updateTime},</if>" +
            "</set>" +
            "WHERE id = #{id}" +
            "</script>")
    int updateUser(User user);
    
    /**
     * 根据ID查询用户
     * @param id 用户ID
     * @return 用户对象
     */
    @Select("SELECT * FROM users WHERE id = #{id}")
    @Results({
            @Result(property = "id", column = "id", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "email", column = "email"),
            @Result(property = "age", column = "age"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    User selectUserById(@Param("id") Integer id);
    
    /**
     * 根据用户名查询用户
     * @param username 用户名
     * @return 用户对象
     */
    @Select("SELECT * FROM users WHERE username = #{username}")
    @Results({
            @Result(property = "id", column = "id", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "email", column = "email"),
            @Result(property = "age", column = "age"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    User selectUserByUsername(@Param("username") String username);
    
    /**
     * 查询所有用户
     * @return 用户列表
     */
    @Select("SELECT * FROM users ORDER BY id")
    @Results({
            @Result(property = "id", column = "id", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "email", column = "email"),
            @Result(property = "age", column = "age"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<User> selectAllUsers();
    
    /**
     * 根据年龄范围查询用户
     * @param minAge 最小年龄
     * @param maxAge 最大年龄
     * @return 用户列表
     */
    @Select("SELECT * FROM users WHERE age &gt;= #{minAge} AND age &lt;= #{maxAge} ORDER BY age")
    @Results({
            @Result(property = "id", column = "id", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "email", column = "email"),
            @Result(property = "age", column = "age"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<User> selectUsersByAgeRange(@Param("minAge") Integer minAge, @Param("maxAge") Integer maxAge);
    
    /**
     * 根据邮箱查询用户
     * @param email 邮箱
     * @return 用户对象
     */
    @Select("SELECT * FROM users WHERE email = #{email}")
    @Results({
            @Result(property = "id", column = "id", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "email", column = "email"),
            @Result(property = "age", column = "age"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    User selectUserByEmail(@Param("email") String email);
    
    /**
     * 统计用户总数
     * @return 用户总数
     */
    @Select("SELECT COUNT(*) FROM users")
    int countUsers();
    
    /**
     * 根据用户名模糊查询
     * @param username 用户名关键字
     * @return 用户列表
     */
    @Select("SELECT * FROM users WHERE username LIKE CONCAT('%', #{username}, '%') ORDER BY id")
    @Results({
            @Result(property = "id", column = "id", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "email", column = "email"),
            @Result(property = "age", column = "age"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<User> selectUsersByUsernameLike(@Param("username") String username);
    
    /**
     * 根据多个条件查询用户（动态SQL示例）
     * @param username 用户名（可选）
     * @param minAge 最小年龄（可选）
     * @param maxAge 最大年龄（可选）
     * @return 用户列表
     */
    @Select("<script>" +
            "SELECT * FROM users " +
            "<where>" +
            "<if test='username != null and username != \"\"'>" +
            "AND username LIKE CONCAT('%', #{username}, '%')" +
            "</if>" +
            "<if test='minAge != null'>" +
            "AND age &gt;= #{minAge}" +
            "</if>" +
            "<if test='maxAge != null'>" +
            "AND age &lt;= #{maxAge}" +
            "</if>" +
            "</where>" +
            "ORDER BY id" +
            "</script>")
    @Results({
            @Result(property = "id", column = "id", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "email", column = "email"),
            @Result(property = "age", column = "age"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<User> selectUsersByConditions(@Param("username") String username,
                                        @Param("minAge") Integer minAge,
                                        @Param("maxAge") Integer maxAge);
    
    /**
     * 批量插入用户
     * @param users 用户列表
     * @return 影响行数
     */
    @Insert("<script>" +
            "INSERT INTO users (username, email, age, phone, create_time, update_time) VALUES " +
            "<foreach collection='list' item='user' separator=','>" +
            "(#{user.username}, #{user.email}, #{user.age}, #{user.phone}, #{user.createTime}, #{user.updateTime})" +
            "</foreach>" +
            "</script>")
    int batchInsertUsers(@Param("list") List<User> users);
    
    /**
     * 根据ID列表查询用户
     * @param ids ID列表
     * @return 用户列表
     */
    @Select("<script>" +
            "SELECT * FROM users WHERE id IN " +
            "<foreach collection='list' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "ORDER BY id" +
            "</script>")
    @Results({
            @Result(property = "id", column = "id", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "email", column = "email"),
            @Result(property = "age", column = "age"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<User> selectUsersByIds(@Param("list") List<Integer> ids);
}
